{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Escaping Excel Hell with Python and Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Minnebar 11, April 23 2016"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, import necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can issue shell commands too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Combining-Multiple-Excel-File-with-Pandas.ipynb\r\n",
      "Common-Excel-Part-2.ipynb\r\n",
      "Google-Forms-Data-Analysis.ipynb\r\n",
      "Ipython-pandas-tips-and-tricks.ipynb\r\n",
      "Learn_Pandas-Weighted_Average.ipynb\r\n",
      "Minnebar\\ 11\\ Demo\\ -\\ Master.ipynb\r\n",
      "Pandas-Pivot-Table-Explained.ipynb\r\n",
      "test.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "!dir"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the excel file (from the github repo) and turn into a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=True\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>sku</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>740150</td>\n",
       "      <td>Barton LLC</td>\n",
       "      <td>B1-20000</td>\n",
       "      <td>39</td>\n",
       "      <td>86.69</td>\n",
       "      <td>3380.91</td>\n",
       "      <td>2014-01-01 07:21:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>S2-77896</td>\n",
       "      <td>-1</td>\n",
       "      <td>63.16</td>\n",
       "      <td>-63.16</td>\n",
       "      <td>2014-01-01 10:00:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>B1-69924</td>\n",
       "      <td>23</td>\n",
       "      <td>90.70</td>\n",
       "      <td>2086.10</td>\n",
       "      <td>2014-01-01 13:24:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>307599</td>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>S1-65481</td>\n",
       "      <td>41</td>\n",
       "      <td>21.05</td>\n",
       "      <td>863.05</td>\n",
       "      <td>2014-01-01 15:05:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412290</td>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>S2-34077</td>\n",
       "      <td>6</td>\n",
       "      <td>83.21</td>\n",
       "      <td>499.26</td>\n",
       "      <td>2014-01-01 23:26:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                         name       sku  quantity  \\\n",
       "0          740150                   Barton LLC  B1-20000        39   \n",
       "1          714466              Trantow-Barrows  S2-77896        -1   \n",
       "2          218895                    Kulas Inc  B1-69924        23   \n",
       "3          307599  Kassulke, Ondricka and Metz  S1-65481        41   \n",
       "4          412290                Jerde-Hilpert  S2-34077         6   \n",
       "\n",
       "   unit price  ext price                 date  \n",
       "0       86.69    3380.91  2014-01-01 07:21:51  \n",
       "1       63.16     -63.16  2014-01-01 10:00:47  \n",
       "2       90.70    2086.10  2014-01-01 13:24:58  \n",
       "3       21.05     863.05  2014-01-01 15:05:22  \n",
       "4       83.21     499.26  2014-01-01 23:26:55  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>sku</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>S1-06532</td>\n",
       "      <td>12</td>\n",
       "      <td>55.80</td>\n",
       "      <td>669.60</td>\n",
       "      <td>2014-12-30 13:38:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>786968</td>\n",
       "      <td>Frami, Hills and Schmidt</td>\n",
       "      <td>S1-06532</td>\n",
       "      <td>37</td>\n",
       "      <td>13.14</td>\n",
       "      <td>486.18</td>\n",
       "      <td>2014-12-30 21:42:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>239344</td>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>S2-10342</td>\n",
       "      <td>14</td>\n",
       "      <td>38.75</td>\n",
       "      <td>542.50</td>\n",
       "      <td>2014-12-30 22:45:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>642753</td>\n",
       "      <td>Pollich LLC</td>\n",
       "      <td>S2-82423</td>\n",
       "      <td>3</td>\n",
       "      <td>65.97</td>\n",
       "      <td>197.91</td>\n",
       "      <td>2014-12-31 10:36:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1499</th>\n",
       "      <td>383080</td>\n",
       "      <td>Will LLC</td>\n",
       "      <td>S2-00301</td>\n",
       "      <td>38</td>\n",
       "      <td>46.44</td>\n",
       "      <td>1764.72</td>\n",
       "      <td>2014-12-31 12:48:35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      account number                          name       sku  quantity  \\\n",
       "1495          737550  Fritsch, Russel and Anderson  S1-06532        12   \n",
       "1496          786968      Frami, Hills and Schmidt  S1-06532        37   \n",
       "1497          239344                    Stokes LLC  S2-10342        14   \n",
       "1498          642753                   Pollich LLC  S2-82423         3   \n",
       "1499          383080                      Will LLC  S2-00301        38   \n",
       "\n",
       "      unit price  ext price                 date  \n",
       "1495       55.80     669.60  2014-12-30 13:38:13  \n",
       "1496       13.14     486.18  2014-12-30 21:42:17  \n",
       "1497       38.75     542.50  2014-12-30 22:45:19  \n",
       "1498       65.97     197.91  2014-12-31 10:36:24  \n",
       "1499       46.44    1764.72  2014-12-31 12:48:35  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1500.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>485957.841333</td>\n",
       "      <td>24.308667</td>\n",
       "      <td>55.007527</td>\n",
       "      <td>1345.856213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>223974.044572</td>\n",
       "      <td>14.439265</td>\n",
       "      <td>25.903267</td>\n",
       "      <td>1084.914881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>141962.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>10.030000</td>\n",
       "      <td>-97.160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>257198.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>32.500000</td>\n",
       "      <td>472.177500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>527099.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>55.465000</td>\n",
       "      <td>1050.390000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>714466.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>77.075000</td>\n",
       "      <td>2068.330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>786968.000000</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>99.850000</td>\n",
       "      <td>4824.540000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       account number     quantity   unit price    ext price\n",
       "count     1500.000000  1500.000000  1500.000000  1500.000000\n",
       "mean    485957.841333    24.308667    55.007527  1345.856213\n",
       "std     223974.044572    14.439265    25.903267  1084.914881\n",
       "min     141962.000000    -1.000000    10.030000   -97.160000\n",
       "25%     257198.000000    12.000000    32.500000   472.177500\n",
       "50%     527099.000000    25.000000    55.465000  1050.390000\n",
       "75%     714466.000000    37.000000    77.075000  2068.330000\n",
       "max     786968.000000    49.000000    99.850000  4824.540000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1500 entries, 0 to 1499\n",
      "Data columns (total 7 columns):\n",
      "account number    1500 non-null int64\n",
      "name              1500 non-null object\n",
      "sku               1500 non-null object\n",
      "quantity          1500 non-null int64\n",
      "unit price        1500 non-null float64\n",
      "ext price         1500 non-null float64\n",
      "date              1500 non-null object\n",
      "dtypes: float64(2), int64(2), object(3)\n",
      "memory usage: 82.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily perform math operations on the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36463"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.quantity.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "82511.289999999994"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"unit price\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can show how to aggregate some data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Barton LLC                         4409.06\n",
       "Cronin, Oberbrunner and Spencer    3336.99\n",
       "Frami, Hills and Schmidt           3942.49\n",
       "Fritsch, Russel and Anderson       4350.41\n",
       "Halvorson, Crona and Champlin      3244.92\n",
       "Herman LLC                         3259.15\n",
       "Jerde-Hilpert                      4635.54\n",
       "Kassulke, Ondricka and Metz        3266.76\n",
       "Keeling LLC                        4223.63\n",
       "Kiehn-Spinka                       4389.32\n",
       "Koepp Ltd                          4459.96\n",
       "Kuhn-Gusikowski                    4075.87\n",
       "Kulas Inc                          5608.19\n",
       "Pollich LLC                        4126.92\n",
       "Purdy-Kunde                        2668.07\n",
       "Sanford and Sons                   4142.25\n",
       "Stokes LLC                         3711.26\n",
       "Trantow-Barrows                    5280.93\n",
       "White-Trantow                      5040.73\n",
       "Will LLC                           4338.84\n",
       "Name: unit price, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('name')[\"unit price\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do multiple groupbys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th>sku</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Barton LLC</th>\n",
       "      <th>B1-04202</th>\n",
       "      <td>35.000000</td>\n",
       "      <td>69.5200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B1-05914</th>\n",
       "      <td>24.000000</td>\n",
       "      <td>13.5300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B1-20000</th>\n",
       "      <td>22.000000</td>\n",
       "      <td>78.9225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B1-33087</th>\n",
       "      <td>22.000000</td>\n",
       "      <td>96.4150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B1-33364</th>\n",
       "      <td>27.666667</td>\n",
       "      <td>40.0800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      quantity  unit price\n",
       "name       sku                            \n",
       "Barton LLC B1-04202  35.000000     69.5200\n",
       "           B1-05914  24.000000     13.5300\n",
       "           B1-20000  22.000000     78.9225\n",
       "           B1-33087  22.000000     96.4150\n",
       "           B1-33364  27.666667     40.0800"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"name\", \"sku\"])[\"quantity\", \"unit price\"].mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time series analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make sure to set the date as a date column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1500 entries, 0 to 1499\n",
      "Data columns (total 7 columns):\n",
      "account number    1500 non-null int64\n",
      "name              1500 non-null object\n",
      "sku               1500 non-null object\n",
      "quantity          1500 non-null int64\n",
      "unit price        1500 non-null float64\n",
      "ext price         1500 non-null float64\n",
      "date              1500 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(2), int64(2), object(2)\n",
      "memory usage: 82.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df[\"date\"] = pd.to_datetime(df['date'])\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a date time object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account number</th>\n",
       "      <th>name</th>\n",
       "      <th>sku</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit price</th>\n",
       "      <th>ext price</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>740150</td>\n",
       "      <td>Barton LLC</td>\n",
       "      <td>B1-20000</td>\n",
       "      <td>39</td>\n",
       "      <td>86.69</td>\n",
       "      <td>3380.91</td>\n",
       "      <td>2014-01-01 07:21:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>S2-77896</td>\n",
       "      <td>-1</td>\n",
       "      <td>63.16</td>\n",
       "      <td>-63.16</td>\n",
       "      <td>2014-01-01 10:00:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>B1-69924</td>\n",
       "      <td>23</td>\n",
       "      <td>90.70</td>\n",
       "      <td>2086.10</td>\n",
       "      <td>2014-01-01 13:24:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>307599</td>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>S1-65481</td>\n",
       "      <td>41</td>\n",
       "      <td>21.05</td>\n",
       "      <td>863.05</td>\n",
       "      <td>2014-01-01 15:05:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412290</td>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>S2-34077</td>\n",
       "      <td>6</td>\n",
       "      <td>83.21</td>\n",
       "      <td>499.26</td>\n",
       "      <td>2014-01-01 23:26:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account number                         name       sku  quantity  \\\n",
       "0          740150                   Barton LLC  B1-20000        39   \n",
       "1          714466              Trantow-Barrows  S2-77896        -1   \n",
       "2          218895                    Kulas Inc  B1-69924        23   \n",
       "3          307599  Kassulke, Ondricka and Metz  S1-65481        41   \n",
       "4          412290                Jerde-Hilpert  S2-34077         6   \n",
       "\n",
       "   unit price  ext price                date  \n",
       "0       86.69    3380.91 2014-01-01 07:21:51  \n",
       "1       63.16     -63.16 2014-01-01 10:00:47  \n",
       "2       90.70    2086.10 2014-01-01 13:24:58  \n",
       "3       21.05     863.05 2014-01-01 15:05:22  \n",
       "4       83.21     499.26 2014-01-01 23:26:55  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how much we sold by month by customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name        date      \n",
       "Barton LLC  2014-01-31     6177.57\n",
       "            2014-02-28    12218.03\n",
       "            2014-03-31     3513.53\n",
       "            2014-04-30    11474.20\n",
       "            2014-05-31    10220.17\n",
       "Name: ext price, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\"].resample(\"M\").sum().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What about every 3 Months?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name        date      \n",
       "Barton LLC  2014-01-31     6177.57\n",
       "            2014-04-30    27205.76\n",
       "            2014-07-31    27434.38\n",
       "            2014-10-31    40946.75\n",
       "            2015-01-31     7674.04\n",
       "Name: ext price, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\"].resample(\"3M\").sum().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What about quarterly ending in Dec?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name                             date      \n",
       "Barton LLC                       2014-03-31    21909.13\n",
       "                                 2014-06-30    32158.10\n",
       "                                 2014-09-30    38345.55\n",
       "                                 2014-12-31    17025.72\n",
       "Cronin, Oberbrunner and Spencer  2014-03-31    26809.63\n",
       "Name: ext price, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\"].resample('Q-DEC').sum().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Annual Number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name                             date      \n",
       "Barton LLC                       2014-12-31    109438.50\n",
       "Cronin, Oberbrunner and Spencer  2014-12-31     89734.55\n",
       "Frami, Hills and Schmidt         2014-12-31    103569.59\n",
       "Fritsch, Russel and Anderson     2014-12-31    112214.71\n",
       "Halvorson, Crona and Champlin    2014-12-31     70004.36\n",
       "Herman LLC                       2014-12-31     82865.00\n",
       "Jerde-Hilpert                    2014-12-31    112591.43\n",
       "Kassulke, Ondricka and Metz      2014-12-31     86451.07\n",
       "Keeling LLC                      2014-12-31    100934.30\n",
       "Kiehn-Spinka                     2014-12-31     99608.77\n",
       "Koepp Ltd                        2014-12-31    103660.54\n",
       "Kuhn-Gusikowski                  2014-12-31     91094.28\n",
       "Kulas Inc                        2014-12-31    137351.96\n",
       "Pollich LLC                      2014-12-31     87347.18\n",
       "Purdy-Kunde                      2014-12-31     77898.21\n",
       "Sanford and Sons                 2014-12-31     98822.98\n",
       "Stokes LLC                       2014-12-31     91535.92\n",
       "Trantow-Barrows                  2014-12-31    123381.38\n",
       "White-Trantow                    2014-12-31    135841.99\n",
       "Will LLC                         2014-12-31    104437.60\n",
       "Name: ext price, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\"].resample('A-DEC').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame vs. Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ext price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>109438.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cronin, Oberbrunner and Spencer</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>89734.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frami, Hills and Schmidt</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>103569.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>112214.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Halvorson, Crona and Champlin</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>70004.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>82865.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>112591.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>86451.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>100934.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>99608.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>103660.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kuhn-Gusikowski</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>91094.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>137351.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pollich LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>87347.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>77898.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sanford and Sons</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>98822.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>91535.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>123381.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>White-Trantow</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>135841.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Will LLC</th>\n",
       "      <th>2014-12-31</th>\n",
       "      <td>104437.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            ext price\n",
       "name                            date                 \n",
       "Barton LLC                      2014-12-31  109438.50\n",
       "Cronin, Oberbrunner and Spencer 2014-12-31   89734.55\n",
       "Frami, Hills and Schmidt        2014-12-31  103569.59\n",
       "Fritsch, Russel and Anderson    2014-12-31  112214.71\n",
       "Halvorson, Crona and Champlin   2014-12-31   70004.36\n",
       "Herman LLC                      2014-12-31   82865.00\n",
       "Jerde-Hilpert                   2014-12-31  112591.43\n",
       "Kassulke, Ondricka and Metz     2014-12-31   86451.07\n",
       "Keeling LLC                     2014-12-31  100934.30\n",
       "Kiehn-Spinka                    2014-12-31   99608.77\n",
       "Koepp Ltd                       2014-12-31  103660.54\n",
       "Kuhn-Gusikowski                 2014-12-31   91094.28\n",
       "Kulas Inc                       2014-12-31  137351.96\n",
       "Pollich LLC                     2014-12-31   87347.18\n",
       "Purdy-Kunde                     2014-12-31   77898.21\n",
       "Sanford and Sons                2014-12-31   98822.98\n",
       "Stokes LLC                      2014-12-31   91535.92\n",
       "Trantow-Barrows                 2014-12-31  123381.38\n",
       "White-Trantow                   2014-12-31  135841.99\n",
       "Will LLC                        2014-12-31  104437.60"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\",].resample('A-DEC').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.set_index('date').groupby('name')[\"ext price\",].resample('A-DEC').sum().to_excel(\"test.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
